/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.Products.Product;
import DTO.Products.Products;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import util.DBUtil;

/**
 *
 * @author ThanhTai
 */
public class ProductsDAO {

    public Products getTopSaleProduct() throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Connection conn = null;
        CallableStatement proc_stm = null;
        String query = "";
        Products products = new Products();
        ResultSet rs = null;

        try {
            products = new Products();
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query = "{ call GetTopSaleProducts() }";
            proc_stm = conn.prepareCall(query);
            rs = proc_stm.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setProductID(rs.getInt("productID"));
                product.setBrandID(rs.getInt("brandID"));
                product.setProductName(rs.getString("productName"));
                product.setProductPrice(rs.getLong("productPrice"));
                product.setProductImage(rs.getString("productImage"));
                product.setShortDecs(rs.getString("shortDecs"));
                product.setLongDecs(rs.getString("longDecs"));
                product.setMonitor(rs.getString("monitor"));
                product.setCpu(rs.getString("cpu"));
                product.setRam(rs.getString("ram"));
                product.setMemory(rs.getString("memory"));
                product.setWifi(rs.getString("wifi"));
                product.setOs(rs.getString("os"));
                product.setPin(rs.getString("pin"));
                product.setOthers(rs.getString("others"));
                product.setSold(rs.getInt("sold"));
                product.setCreateDate(rs.getLong("createDate"));
                product.setStatus(rs.getString("status"));
                products.addProduct(product);
            }
            return products;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    public Products getNewProduct() throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Connection conn = null;
        CallableStatement proc_stm = null;
        String query = "";
        Products products = new Products();
        ResultSet rs = null;

        try {
            products = new Products();
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query = "{ call GetNewProducts() }";
            proc_stm = conn.prepareCall(query);
            rs = proc_stm.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setProductID(rs.getInt("productID"));
                product.setBrandID(rs.getInt("brandID"));
                product.setProductName(rs.getString("productName"));
                product.setProductPrice(rs.getLong("productPrice"));
                product.setProductImage(rs.getString("productImage"));
                product.setShortDecs(rs.getString("shortDecs"));
                product.setLongDecs(rs.getString("longDecs"));
                product.setMonitor(rs.getString("monitor"));
                product.setCpu(rs.getString("cpu"));
                product.setRam(rs.getString("ram"));
                product.setMemory(rs.getString("memory"));
                product.setWifi(rs.getString("wifi"));
                product.setOs(rs.getString("os"));
                product.setPin(rs.getString("pin"));
                product.setOthers(rs.getString("others"));
                product.setSold(rs.getInt("sold"));
                product.setCreateDate(rs.getLong("createDate"));
                product.setStatus(rs.getString("status"));
                products.addProduct(product);
            }
            return products;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    public Products getAllProduct() {
        DBUtil util = new DBUtil();
        Products pro = new Products();
        Connection con = null;

        try {
            ResultSet rs = null;
            PreparedStatement pr = null;
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            con = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());

            pr = con.prepareStatement("select * from product where  status='Available'");
            rs = pr.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setProductID(rs.getInt("productID"));
                product.setBrandID(rs.getInt("brandID"));
                product.setProductName(rs.getString("productName"));
                product.setProductPrice(rs.getLong("productPrice"));
                product.setProductImage(rs.getString("productImage"));
                product.setShortDecs(rs.getString("shortDecs"));
                product.setLongDecs(rs.getString("longDecs"));
                product.setMonitor(rs.getString("monitor"));
                product.setCpu(rs.getString("cpu"));
                product.setRam(rs.getString("ram"));
                product.setMemory(rs.getString("memory"));
                product.setWifi(rs.getString("wifi"));
                product.setOs(rs.getString("os"));
                product.setPin(rs.getString("pin"));
                product.setOthers(rs.getString("others"));
                product.setSold(rs.getInt("sold"));
                product.setCreateDate(rs.getLong("createDate"));
                product.setStatus(rs.getString("status"));
                pro.addProduct(product);
            }
            pr.close();
            rs.close();
            con.close();
            return pro;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
